cd C:\Users\alen_\Dropbox\burning_glass\data


u county_naics3_emp_2020_q1, clear

ren month2_emplvl emp_2020

merge 1:1 county naics3 using county_naics3_emp_2019_q4
drop _merge 

ren month2_emplvl emp_2019

merge 1:1 county naics3 using county_naics3_emp_2024_q1
drop _merge 

ren month2_emplvl emp_2022

merge 1:1 county naics3 using county_naics3_emp_2015_q4
drop _merge 

ren month2_emplvl emp_2017 

merge m:1 county using county_metrofips 
keep if _merge==3
drop _merge 

collapse (sum) emp_*, by(naics3 metrofips)

g ln_population2017=ln(emp_2017)
g ln_population2019=ln(emp_2019)

g dln_emp_2022=ln(emp_2022)-ln(emp_2020)
g dln_emp_2020=ln(emp_2019)-ln(emp_2017)


merge m:1 metrofips using dpop_metro_2020_2023
keep if _merge==3
drop _merge

reshape long dln_emp_ dln_emp_county_, i(metrofips naics3) j(year)

egen naics_id=group(naics3)

bysort metrofips year: egen total_emp_2017=total(emp_2017)
bysort metrofips year: egen total_emp_2019=total(emp_2019)

g remote_ind=0
replace remote_ind=1 if naics3>=510 & naics3<=519
replace remote_ind=1 if naics3>=520 & naics3<=529
replace remote_ind=1 if naics3>=540 & naics3<=549

merge m:1 naics3 using "remote_fraction_based_on_bg_naics_2020_2022"
drop if _merge==2
drop _merge 

merge m:1 naics3 using "bloom_measure_remote_ind"
drop if _merge==2
drop _merge 

drop if naics3>=900

g remote=0 if dremote_fraction<0.064
replace remote=1 if dremote_fraction>0.095 & dremote_fraction<.

g remote_bloom=0 if dpercent<4.9
replace remote_bloom=1 if dpercent>8.8 & dpercent<.

**** Regression on local employment size (controlling for occupational x year)

g ln_total_emp_2017=ln(total_emp_2017)
g ln_total_emp_2019=ln(total_emp_2019)

g ln_total_emp=ln_total_emp_2017 if year==2020
replace ln_total_emp=ln_total_emp_2019 if year==2022

g total_emp=total_emp_2017 if year==2020
replace total_emp=total_emp_2019 if year==2022

g local_service=0
replace local_service=1 if naics3>=230 & naics3<=239
replace local_service=1 if naics3>=441 & naics3<=459
replace local_service=1 if naics3==485
replace local_service=1 if naics3>=531 & naics3<=533
replace local_service=1 if naics3==562
replace local_service=1 if naics3>=610 & naics3<=619
replace local_service=1 if naics3>=620 & naics3<=629
replace local_service=1 if naics3>=710 & naics3<=719
replace local_service=1 if naics3>=720 & naics3<=729
replace local_service=1 if naics3==811 | naics3==812

g emp=emp_2020 if year==2022 
replace emp=emp_2017 if year==2020

g ln_emp=ln(emp)

g dpop=dpop2020 if year==2020 
replace dpop=dpop2023 if year==2022








** Table 3 
* Column 1 
reghdfe dln_emp_ c.ln_emp##i.remote##i.year  [w=total_emp], absorb( i.naics_id##i.year) cluster(metrofips)
* Column 2
reghdfe dln_emp_ c.dpop##i.year##i.naics_id  c.ln_emp##i.remote##i.year  [w=total_emp] if local_service==0, absorb( i.naics_id##i.year) cluster(metrofips)
* Column 3
reghdfe dln_emp_ c.ln_emp##i.remote_ind##i.year [w=total_emp] , absorb( i.naics_id##i.year) cluster(metrofips)
* Column 4
reghdfe dln_emp_ c.dpop##i.year##i.naics_id  c.ln_emp##i.remote_ind##i.year [w=total_emp] if local_service==0 , absorb( i.naics_id##i.year) cluster(metrofips)

***********************************************************

* Table A1
* Column 3
reghdfe dln_emp_ c.ln_emp##i.year  [w=total_emp], absorb( i.naics_id##i.year) cluster(metrofips)
* Column 4
reghdfe dln_emp_ c.ln_total_emp##i.year  [w=total_emp], absorb( i.naics_id##i.year) cluster(metrofips)

*************************************

*** Table A4



***** UWP based on QCEW 
cd "C:\Users\alen_\Dropbox\burning_glass\data"

clear all


g time=.

append using county_naics3_earnings_2018_q1
replace time=20181 if time==.

append using county_naics3_earnings_2018_q2 
replace time=20182 if time==.

append using county_naics3_earnings_2018_q3 
replace time=20183 if time==.

append using county_naics3_earnings_2018_q4 
replace time=20184 if time==.

append using county_naics3_earnings_2019_q1 
replace time=20191 if time==.

append using county_naics3_earnings_2019_q2 
replace time=20192 if time==.

append using county_naics3_earnings_2019_q3 
replace time=20193 if time==.

append using county_naics3_earnings_2019_q4 
replace time=20194 if time==.

append using county_naics3_earnings_2020_q1 
replace time=20201 if time==.

append using county_naics3_earnings_2020_q2 
replace time=20202 if time==.

append using county_naics3_earnings_2020_q3 
replace time=20203 if time==.

append using county_naics3_earnings_2020_q4 
replace time=20204 if time==.

append using county_naics3_earnings_2021_q1 
replace time=20211 if time==.

append using county_naics3_earnings_2021_q2 
replace time=20212 if time==.

append using county_naics3_earnings_2021_q3 
replace time=20213 if time==.

append using county_naics3_earnings_2021_q4 
replace time=20214 if time==.

append using county_naics3_earnings_2022_q1 
replace time=20221 if time==.

append using county_naics3_earnings_2022_q2 
replace time=20222 if time==.

append using county_naics3_earnings_2022_q3 
replace time=20223 if time==.

append using county_naics3_earnings_2022_q4 
replace time=20224 if time==.

append using county_naics3_earnings_2023_q1 
replace time=20231 if time==.

append using county_naics3_earnings_2023_q2 
replace time=20232 if time==.

append using county_naics3_earnings_2023_q3 
replace time=20233 if time==.

append using county_naics3_earnings_2023_q4 
replace time=20234 if time==.

merge m:1 county naics3 using county_naics3_emp_2018_q1
drop _merge 

merge m:1 county using county_metrofips 
keep if _merge==3
drop _merge 

ren month2_emplvl emp

g ln_wage=ln( avg_wkly_wage)
g ln_emp=ln(emp)

bysort metrofips time: egen emp_msa=total(emp)


merge m:1 naics using "remote_fraction_based_on_bg_naics_2020_2022"
drop if _merge==2
drop _merge 

capture drop post
g post=0
replace post=1 if time>=20202

drop if naics3>=900

capture drop remote
g remote=0 if dremote_fraction<0.064
*replace remote=1 if dpercent>=13 & dpercent<.
replace remote=1 if dremote_fraction>0.095 & dremote_fraction<.
/*replace remote=1 if dremote_fraction>=0.11 & dremote_fraction<0.2
replace remote=2 if dremote_fraction>0.12
*/

merge m:1 metrofips using dpop_metro_2020_2023
drop if _merge==2
drop _merge

g local_service=0
replace local_service=1 if naics3>=230 & naics3<=239
replace local_service=1 if naics3>=441 & naics3<=459
replace local_service=1 if naics3==485
replace local_service=1 if naics3>=531 & naics3<=533
replace local_service=1 if naics3==562
replace local_service=1 if naics3>=610 & naics3<=619
replace local_service=1 if naics3>=620 & naics3<=629
replace local_service=1 if naics3>=710 & naics3<=719
replace local_service=1 if naics3>=720 & naics3<=729
replace local_service=1 if naics3==811 | naics3==812

g year=floor(time/10)
* Combine the pre-2020 years into one time group
replace year=2019 if year==2018
***********************
* Table A4
* Column 1
reghdfe ln_wage i.post##i.remote##c.ln_emp [w=emp_msa], absorb(naics metrofips) cluster(metrofips)
* Column 2
reghdfe ln_wage i.year##i.remote##c.ln_emp [w=emp_msa], absorb(naics metrofips) cluster(metrofips)
* Column 3
reghdfe ln_wage c.dpop*##i.year##i.remote i.post##i.remote##c.ln_emp [w=emp_msa] if local_service==0, absorb(naics metrofips) cluster(metrofips)


*******************************
*** Table A6

*** Time horizon 1 year

u county_naics3_emp_2020_q1, clear

ren month2_emplvl emp_2020

merge 1:1 county naics3 using county_naics3_emp_2019_q4
drop _merge 

ren month2_emplvl emp_2019

merge 1:1 county naics3 using county_naics3_emp_2021_q1
drop _merge 

ren month2_emplvl emp_2022

merge 1:1 county naics3 using county_naics3_emp_2018_q4
drop _merge 

ren month2_emplvl emp_2017 

merge m:1 county using county_metrofips 
keep if _merge==3
drop _merge 

collapse (sum) emp_*, by(naics3 metrofips)

g ln_population2017=ln(emp_2017)
g ln_population2019=ln(emp_2019)

g dln_emp_2022=ln(emp_2022)-ln(emp_2020)
g dln_emp_2020=ln(emp_2019)-ln(emp_2017)

merge m:1 metrofips using metrofips_population
drop _merge

merge m:1 metrofips using dpop_metro_2020_2023
keep if _merge==3
drop _merge

reshape long dln_emp_ dln_emp_county_, i(metrofips naics3) j(year)

egen naics_id=group(naics3)

g ln_population_metrofips=ln(population_metrofips)
bysort metrofips year: egen total_emp_2017=total(emp_2017)
bysort metrofips year: egen total_emp_2019=total(emp_2019)

g remote_ind=0
replace remote_ind=1 if naics3>=510 & naics3<=519
replace remote_ind=1 if naics3>=520 & naics3<=529
replace remote_ind=1 if naics3>=540 & naics3<=549

merge m:1 naics3 using "remote_fraction_based_on_bg_naics_2020_2022"
drop if _merge==2
drop _merge 

merge m:1 naics3 using "bloom_measure_remote_ind"
drop if _merge==2
drop _merge 

drop if naics3>=900

g remote=0 if dremote_fraction<0.064
replace remote=1 if dremote_fraction>0.095 & dremote_fraction<.

g remote_bloom=0 if dpercent<4.9
replace remote_bloom=1 if dpercent>8.8 & dpercent<.

**** Regression on local employment size (controlling for occupational x year)

g ln_total_emp_2017=ln(total_emp_2017)
g ln_total_emp_2019=ln(total_emp_2019)

g ln_total_emp=ln_total_emp_2017 if year==2020
replace ln_total_emp=ln_total_emp_2019 if year==2022

g total_emp=total_emp_2017 if year==2020
replace total_emp=total_emp_2019 if year==2022

g ln_population=ln_population2017 if year==2020
replace ln_population=ln_population2019 if year==2022

g local_service=0
replace local_service=1 if naics3>=230 & naics3<=239
replace local_service=1 if naics3>=441 & naics3<=459
replace local_service=1 if naics3==485
replace local_service=1 if naics3>=531 & naics3<=533
replace local_service=1 if naics3==562
replace local_service=1 if naics3>=610 & naics3<=619
replace local_service=1 if naics3>=620 & naics3<=629
replace local_service=1 if naics3>=710 & naics3<=719
replace local_service=1 if naics3>=720 & naics3<=729
replace local_service=1 if naics3==811 | naics3==812

g emp=emp_2020 if year==2022 
replace emp=emp_2017 if year==2020

g ln_emp=ln(emp)

g dpop=dpop2020 if year==2020 
replace dpop=dpop2023 if year==2022

* Column 1
reghdfe dln_emp_ c.dpop##i.year##i.naics_id  c.ln_emp##i.remote##i.year  [w=total_emp] if local_service==0, absorb( i.naics_id##i.year) cluster(metrofips)


***

** Time horizon: 2 years
u county_naics3_emp_2020_q1, clear

ren month2_emplvl emp_2020

merge 1:1 county naics3 using county_naics3_emp_2019_q4
drop _merge 

ren month2_emplvl emp_2019

merge 1:1 county naics3 using county_naics3_emp_2022_q1
drop _merge 

ren month2_emplvl emp_2022

merge 1:1 county naics3 using county_naics3_emp_2017_q4
drop _merge 

ren month2_emplvl emp_2017 

merge m:1 county using county_metrofips 
keep if _merge==3
drop _merge 

collapse (sum) emp_*, by(naics3 metrofips)

g ln_population2017=ln(emp_2017)
g ln_population2019=ln(emp_2019)

g dln_emp_2022=ln(emp_2022)-ln(emp_2020)
g dln_emp_2020=ln(emp_2019)-ln(emp_2017)

merge m:1 metrofips using metrofips_population
drop _merge

merge m:1 metrofips using dpop_metro_2020_2023
keep if _merge==3
drop _merge

reshape long dln_emp_ dln_emp_county_, i(metrofips naics3) j(year)

egen naics_id=group(naics3)

g ln_population_metrofips=ln(population_metrofips)
bysort metrofips year: egen total_emp_2017=total(emp_2017)
bysort metrofips year: egen total_emp_2019=total(emp_2019)

g remote_ind=0
replace remote_ind=1 if naics3>=510 & naics3<=519
replace remote_ind=1 if naics3>=520 & naics3<=529
replace remote_ind=1 if naics3>=540 & naics3<=549

merge m:1 naics3 using "remote_fraction_based_on_bg_naics_2020_2022"
drop if _merge==2
drop _merge 

merge m:1 naics3 using "bloom_measure_remote_ind"
drop if _merge==2
drop _merge 

drop if naics3>=900

g remote=0 if dremote_fraction<0.064
replace remote=1 if dremote_fraction>0.095 & dremote_fraction<.

g remote_bloom=0 if dpercent<4.9
replace remote_bloom=1 if dpercent>8.8 & dpercent<.

**** Regression on local employment size (controlling for occupational x year)

g ln_total_emp_2017=ln(total_emp_2017)
g ln_total_emp_2019=ln(total_emp_2019)

g ln_total_emp=ln_total_emp_2017 if year==2020
replace ln_total_emp=ln_total_emp_2019 if year==2022

g total_emp=total_emp_2017 if year==2020
replace total_emp=total_emp_2019 if year==2022

g ln_population=ln_population2017 if year==2020
replace ln_population=ln_population2019 if year==2022

g local_service=0
replace local_service=1 if naics3>=230 & naics3<=239
replace local_service=1 if naics3>=441 & naics3<=459
replace local_service=1 if naics3==485
replace local_service=1 if naics3>=531 & naics3<=533
replace local_service=1 if naics3==562
replace local_service=1 if naics3>=610 & naics3<=619
replace local_service=1 if naics3>=620 & naics3<=629
replace local_service=1 if naics3>=710 & naics3<=719
replace local_service=1 if naics3>=720 & naics3<=729
replace local_service=1 if naics3==811 | naics3==812

g emp=emp_2020 if year==2022 
replace emp=emp_2017 if year==2020

g ln_emp=ln(emp)

g dpop=dpop2020 if year==2020 
replace dpop=dpop2023 if year==2022

* Column 2
reghdfe dln_emp_ c.dpop##i.year##i.naics_id  c.ln_emp##i.remote##i.year  [w=total_emp] if local_service==0, absorb( i.naics_id##i.year) cluster(metrofips)

******


** Time horizon: 3 years
u county_naics3_emp_2020_q1, clear

ren month2_emplvl emp_2020

merge 1:1 county naics3 using county_naics3_emp_2019_q4
drop _merge 

ren month2_emplvl emp_2019

merge 1:1 county naics3 using county_naics3_emp_2023_q1
drop _merge 

ren month2_emplvl emp_2022

merge 1:1 county naics3 using county_naics3_emp_2016_q4
drop _merge 

ren month2_emplvl emp_2017 

merge m:1 county using county_metrofips 
keep if _merge==3
drop _merge 

collapse (sum) emp_*, by(naics3 metrofips)

g ln_population2017=ln(emp_2017)
g ln_population2019=ln(emp_2019)

g dln_emp_2022=ln(emp_2022)-ln(emp_2020)
g dln_emp_2020=ln(emp_2019)-ln(emp_2017)

merge m:1 metrofips using metrofips_population
drop _merge

merge m:1 metrofips using dpop_metro_2020_2023
keep if _merge==3
drop _merge

reshape long dln_emp_ dln_emp_county_, i(metrofips naics3) j(year)

egen naics_id=group(naics3)

g ln_population_metrofips=ln(population_metrofips)
bysort metrofips year: egen total_emp_2017=total(emp_2017)
bysort metrofips year: egen total_emp_2019=total(emp_2019)

g remote_ind=0
replace remote_ind=1 if naics3>=510 & naics3<=519
replace remote_ind=1 if naics3>=520 & naics3<=529
replace remote_ind=1 if naics3>=540 & naics3<=549

merge m:1 naics3 using "remote_fraction_based_on_bg_naics_2020_2022"
drop if _merge==2
drop _merge 

merge m:1 naics3 using "bloom_measure_remote_ind"
drop if _merge==2
drop _merge 

drop if naics3>=900

g remote=0 if dremote_fraction<0.064
replace remote=1 if dremote_fraction>0.095 & dremote_fraction<.

g remote_bloom=0 if dpercent<4.9
replace remote_bloom=1 if dpercent>8.8 & dpercent<.

**** Regression on local employment size (controlling for occupational x year)

g ln_total_emp_2017=ln(total_emp_2017)
g ln_total_emp_2019=ln(total_emp_2019)

g ln_total_emp=ln_total_emp_2017 if year==2020
replace ln_total_emp=ln_total_emp_2019 if year==2022

g total_emp=total_emp_2017 if year==2020
replace total_emp=total_emp_2019 if year==2022

g ln_population=ln_population2017 if year==2020
replace ln_population=ln_population2019 if year==2022

g local_service=0
replace local_service=1 if naics3>=230 & naics3<=239
replace local_service=1 if naics3>=441 & naics3<=459
replace local_service=1 if naics3==485
replace local_service=1 if naics3>=531 & naics3<=533
replace local_service=1 if naics3==562
replace local_service=1 if naics3>=610 & naics3<=619
replace local_service=1 if naics3>=620 & naics3<=629
replace local_service=1 if naics3>=710 & naics3<=719
replace local_service=1 if naics3>=720 & naics3<=729
replace local_service=1 if naics3==811 | naics3==812

g emp=emp_2020 if year==2022 
replace emp=emp_2017 if year==2020

g ln_emp=ln(emp)

g dpop=dpop2020 if year==2020 
replace dpop=dpop2023 if year==2022

* Column 3
reghdfe dln_emp_ c.dpop##i.year##i.naics_id  c.ln_emp##i.remote##i.year  [w=total_emp] if local_service==0, absorb( i.naics_id##i.year) cluster(metrofips)

*** Placebo (2 year 2015-2017; 2017-2020)

** Time horizon: 2 years
u county_naics3_emp_2018_q1, clear

ren month2_emplvl emp_2020

merge 1:1 county naics3 using county_naics3_emp_2017_q4
drop _merge 

ren month2_emplvl emp_2019

merge 1:1 county naics3 using county_naics3_emp_2020_q1
drop _merge 

ren month2_emplvl emp_2022

merge 1:1 county naics3 using county_naics3_emp_2015_q4
drop _merge 

ren month2_emplvl emp_2017 

merge m:1 county using county_metrofips 
keep if _merge==3
drop _merge 

collapse (sum) emp_*, by(naics3 metrofips)

g ln_population2017=ln(emp_2017)
g ln_population2019=ln(emp_2019)

g dln_emp_2022=ln(emp_2022)-ln(emp_2020)
g dln_emp_2020=ln(emp_2019)-ln(emp_2017)

merge m:1 metrofips using metrofips_population
drop _merge

merge m:1 metrofips using dpop_metro_2020_2023
keep if _merge==3
drop _merge

reshape long dln_emp_ dln_emp_county_, i(metrofips naics3) j(year)

egen naics_id=group(naics3)

g ln_population_metrofips=ln(population_metrofips)
bysort metrofips year: egen total_emp_2017=total(emp_2017)
bysort metrofips year: egen total_emp_2019=total(emp_2019)

g remote_ind=0
replace remote_ind=1 if naics3>=510 & naics3<=519
replace remote_ind=1 if naics3>=520 & naics3<=529
replace remote_ind=1 if naics3>=540 & naics3<=549

merge m:1 naics3 using "remote_fraction_based_on_bg_naics_2020_2022"
drop if _merge==2
drop _merge 

merge m:1 naics3 using "bloom_measure_remote_ind"
drop if _merge==2
drop _merge 

drop if naics3>=900

g remote=0 if dremote_fraction<0.064
replace remote=1 if dremote_fraction>0.095 & dremote_fraction<.

g remote_bloom=0 if dpercent<4.9
replace remote_bloom=1 if dpercent>8.8 & dpercent<.

**** Regression on local employment size (controlling for occupational x year)

g ln_total_emp_2017=ln(total_emp_2017)
g ln_total_emp_2019=ln(total_emp_2019)

g ln_total_emp=ln_total_emp_2017 if year==2020
replace ln_total_emp=ln_total_emp_2019 if year==2022

g total_emp=total_emp_2017 if year==2020
replace total_emp=total_emp_2019 if year==2022

g ln_population=ln_population2017 if year==2020
replace ln_population=ln_population2019 if year==2022

g local_service=0
replace local_service=1 if naics3>=230 & naics3<=239
replace local_service=1 if naics3>=441 & naics3<=459
replace local_service=1 if naics3==485
replace local_service=1 if naics3>=531 & naics3<=533
replace local_service=1 if naics3==562
replace local_service=1 if naics3>=610 & naics3<=619
replace local_service=1 if naics3>=620 & naics3<=629
replace local_service=1 if naics3>=710 & naics3<=719
replace local_service=1 if naics3>=720 & naics3<=729
replace local_service=1 if naics3==811 | naics3==812

g emp=emp_2020 if year==2022 
replace emp=emp_2017 if year==2020

g ln_emp=ln(emp)

g dpop=dpop2020 if year==2020 
replace dpop=dpop2023 if year==2022

* Column 4
reghdfe dln_emp_ c.dpop##i.year##i.naics_id  c.ln_emp##i.remote##i.year  [w=total_emp] if local_service==0, absorb( i.naics_id##i.year) cluster(metrofips)


